package com.testing.common;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Classname MysqlUtils
 * @Description 用于完成数据库的连接操作
 * @Date 2022/10/14 22:31
 * @Created by 特斯汀Roy
 */
public class MysqlUtils {
    //数据库的连接
    private Connection myCon;
    private String url = "jdbc:mysql://39.108.55.18:3306/tpshop3.0?useUnicode=true&useSSL=false&characterEncoding=utf-8";
    private String user = "guest";
    private String password = "testing123";

    public Connection getMyCon() {
        return myCon;
    }

    public MysqlUtils() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            myCon = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("创建数据库连接失败");
        }
    }

    public MysqlUtils(String url, String user, String password) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            myCon = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            System.out.println("创建数据库连接失败");
        }
    }

    public List<Map<String, String>> queryDbData(String sql) {
        try {
            //创建查询
            Statement statement = myCon.createStatement();
            //获取到一个结果集
            ResultSet resultSet = statement.executeQuery(sql);
            //用LIst<Map<String,String>>来存储数据结果
            List<Map<String, String>> dbDataList = new ArrayList<>();
            //每一条数据，都是{字段=值}
            ResultSetMetaData headInfo = resultSet.getMetaData();
            //数据库的字段读取的时候从1开始计数
            //获取每一条数据,判断并且取下一行数据
            while (resultSet.next()) {
                //每一条数据都是一个Map，遍历一行数据存储进去
                Map<String, String> onLineData = new HashMap<>();
                for (int i = 1; i <= headInfo.getColumnCount(); i++) {
                    onLineData.put(headInfo.getColumnName(i), resultSet.getString(i));
                }
                //将这一行数据的map存到list里面
                dbDataList.add(onLineData);
            }
            System.out.println(dbDataList);
            return dbDataList;
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("查询数据失败");
            ArrayList<Map<String, String>> maps = new ArrayList<>();
            maps.add(new HashMap<String, String>() {{
                put("error", "查询数据失败");
            }});
            return maps;
        }

    }


}
